﻿using DataObjects;
using InfrastructureSqlServer.Helpers;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class EmpresaDAO
    {

        private List<Empresa> CarregarListaEmpresa(SqlDataReader dr)
        {
            List<Empresa> liEmpresa = new List<Empresa>();

            while (dr.Read())
            {
                Empresa empresa = new Empresa();
                empresa.EmpresaId = int.Parse(dr["EMPRESAID"].ToString());
                empresa.CNPJ = dr["CNPJ"].ToString();
                empresa.Descricao = dr["DESCRICAO"].ToString();
                liEmpresa.Add(empresa);
            }

            return liEmpresa;
        }

        public void Inserir(Empresa _empresa)
        {
            SqlParameter[] paramsToSP = new SqlParameter[2];

            paramsToSP[0] = new SqlParameter("@descricao", _empresa.Descricao);
            paramsToSP[1] = new SqlParameter("@CNPJ", _empresa.CNPJ);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.StoredProcedure, "stp_insert_empresa", paramsToSP);

            }
            catch (Exception e)
            {
                throw new Exception("Erro ao inserir empresa, Erro: " + e.Message);
            }

        }

        public bool Alterar(Empresa _empresa)
        {
            SqlParameter[] paramsToSP = new SqlParameter[3];

            paramsToSP[0] = new SqlParameter("@empresaid", _empresa.EmpresaId);
            paramsToSP[1] = new SqlParameter("@descricao", _empresa.Descricao);
            paramsToSP[2] = new SqlParameter("@cnpj", _empresa.CNPJ);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.StoredProcedure, "stp_update_empresa", paramsToSP);
                return true;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao alterar empresa, Erro: " + e.Message);
            }

        }

        public bool Deletar(int _empresaId)
        {
            SqlParameter[] paramsToSP = new SqlParameter[1];

            paramsToSP[0] = new SqlParameter("@empresaid", _empresaId);

            try
            {
                SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.StoredProcedure, "stp_delete_empresa", paramsToSP);
                return true;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao deletar empresa, Erro: " + e.Message);
            }

        }

        public List<Empresa> Consultar(int _empresaId)
        {
            string sqlQuery = string.Format("SELECT * FROM EMPRESA WHERE EMPRESAID = {0}", _empresaId);

            try
            {
                return CarregarListaEmpresa(SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.Text, sqlQuery));
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao consultar empresa, Erro: " + e.Message);
            }

        }

        public List<Empresa> Consultar(string _empresa)
        {
            string sqlQuery = string.Format("SELECT * FROM EMPRESA WHERE DESCRICAO LIKE '%{0}%'", _empresa);

            try
            {
                return CarregarListaEmpresa(SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.Text, sqlQuery));
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao consultar empresa, Erro: " + e.Message);
            }

        }

        public List<Empresa> Consultar()
        {
            string sqlQuery = string.Format("SELECT * FROM EMPRESA ");

            try
            {
                return CarregarListaEmpresa(SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["conexao"].ToString(),
                                            CommandType.Text, sqlQuery));
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao consultar empresa, Erro: " + e.Message);
            }

        }

    }//class
}//namespace
